package zy.dao.base.area.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.area.AreaDAO;
import zy.entity.base.area.T_Base_Area;

@Repository
public class AreaDAOImpl extends BaseDaoImpl implements AreaDAO{

	@Override
	public List<T_Base_Area> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object ar_upcode = param.get("ar_upcode");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ar_id,ar_code,ar_name,ar_upcode,ar_spell,companyid");
		sql.append(" FROM t_base_area t");
		sql.append(" WHERE 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" AND INSTR(t.ar_name,:name)>0");
        }
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.ar_code,:searchContent)>0 OR INSTR(t.ar_name,:searchContent)>0 OR INSTR(t.ar_spell,:searchContent)>0)");
        }
        if(null != ar_upcode && !"".equals(ar_upcode)){
        	sql.append(" AND (ar_code = :ar_upcode OR ar_upcode = :ar_upcode)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ar_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Area.class));
	}

	@Override
	public Integer queryByName(T_Base_Area area) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ar_id FROM t_base_area");
		sql.append(" WHERE 1=1");
		if(null != area.getAr_name() && !"".equals(area.getAr_name())){
			sql.append(" AND ar_name=:ar_name");
		}
		if(null != area.getAr_id() && area.getAr_id() > 0){
			sql.append(" AND ar_id <> :ar_id");
		}
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(area)
					,Integer.class);
			return id;
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Base_Area queryByID(Integer ar_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ar_id,ar_code,ar_name,ar_upcode,ar_spell,companyid ");
		sql.append(" FROM t_base_area");
		sql.append(" WHERE ar_id=:ar_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ar_id", ar_id),
					new BeanPropertyRowMapper<>(T_Base_Area.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Base_Area area) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(ar_code+0)) from t_base_area ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(area), String.class);
		area.setAr_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_area");
		sql.append(" (ar_code,ar_name,ar_upcode,ar_spell,companyid)");
		sql.append(" VALUES(:ar_code,:ar_name,:ar_upcode,:ar_spell,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(area),holder);
		int id = holder.getKey().intValue();
		area.setAr_id(id);
	}

	@Override
	public void update(T_Base_Area area) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_area");
		sql.append(" SET ar_name=:ar_name,ar_spell=:ar_spell");
		sql.append(" WHERE ar_id=:ar_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(area));
	}

	@Override
	public void del(Integer ar_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_area");
		sql.append(" WHERE ar_id=:ar_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ar_id", ar_id));
	}

}
